from datetime import date, datetime
from http import client
from sqlalchemy import case, func, insert, select, update
from app.api.admin.model.coupon.coupon import CouponCateModel, CouponModel
from app.api.admin.model.order.order import OrderModel
from app.api.h5.schema.coupon.coupon import CouponOutSchema
from app.api.h5.schema.order.order import OrderAppCancleInSchema, OrderAppListInSchema, OrderAppOutSchema, OrderDetailInSchema, OrderInSchema, OrderListInSchema, OrderOutSchema
from app.common.enums import CouponCate
from app.dependencies.database import db
from app.exceptions.exception import AppException
from app.exceptions.http_resp import HttpResp


class OrderService:
    """
    订单服务
    """
    @ classmethod
    async def instance(cls):
        return cls()

    async def add(self, client_id: int, order_in: OrderInSchema):
        coupon_raw = await db.fetch_one(
            select(CouponModel, CouponCateModel.name.label("cate_name"),)
            .outerjoin(CouponCateModel, CouponModel.cate_id == CouponCateModel.key)
            .where(CouponModel.id == order_in.coupon_id)
        )
        coupon = CouponOutSchema.model_validate(
            coupon_raw, from_attributes=True)
        coupon_total = coupon.total_num
        coupon_use = coupon.use_num
        coupon_get = coupon.get_num
        # 判断优惠券是否过期
        if coupon.end_time < datetime.now():
            raise AppException(HttpResp.FAILED, message="优惠券已过期")
        # 判断优惠券是否在线
        if coupon.status == 0:
            raise AppException(HttpResp.FAILED, message="优惠券已下架")
        #  判断优惠券是否已领完
        if coupon_total <= coupon_get:
            print("coupon 状态", coupon_total, coupon_get)
            raise AppException(
                HttpResp.FAILED, message="优惠券已领完")
        # 判断优惠券是否重复领取
        order = await db.fetch_all(
            select(OrderModel)
            .where(OrderModel.coupon_id == order_in.coupon_id)
            .where(OrderModel.client_id == client_id)
        )
        order_count = len(order)
        coupon_limit_get_num = coupon.limit_get_num

        if coupon_limit_get_num != 0 and order_count >= coupon_limit_get_num:
            raise AppException(HttpResp.FAILED, message="优惠券已领取")

        # 修改优惠券领取数量
        await db.execute(update(CouponModel).where(CouponModel.id == order_in.coupon_id).values({"get_num": coupon_get + 1}))

        # 生成订单
        now = datetime.now()
        qr_code = datetime.now().strftime("%Y%m%d%H%M%S") + str(now.microsecond)
        order = {}
        order.update({"coupon_id": order_in.coupon_id})
        order.update({"created_time": datetime.now()})
        order.update({"status": 1})
        order.update({"qr_code": qr_code})
        order.update({"client_id": client_id})

        # order.update({"client_id": 1})
        return await db.execute(insert(OrderModel).values(**order))

    async def list(self, client_id: int, list_in: OrderListInSchema):
        '''
        status：（1待使用2已核销3过期）
        '''
        print("list_in", list_in.model_dump())
        where = []
        if list_in.status != 0:
            where.append(OrderModel.status == list_in.status)
        where.append(OrderModel.client_id == client_id)
        offset = (list_in.page - 1) * list_in.page_size
        query = (select(
            OrderModel,
            CouponModel.id.label("coupon_id"),
            CouponModel.name.label("coupon_name"),
            CouponModel.min_amount,
            CouponModel.discount_amount,
            CouponModel.min_amount,
            CouponModel.start_time,
            CouponModel.end_time,
            CouponModel.description,
            CouponCateModel.id.label("coupon_cate_id"),
            CouponCateModel.name.label("coupon_cate_name"),
        )
            .select_from(OrderModel)
            .outerjoin(CouponModel, CouponModel.id == OrderModel.coupon_id)
            .outerjoin(CouponCateModel, CouponCateModel.key == CouponModel.cate_id)
            .where(*where)
            .offset(offset)
            .limit(list_in.page_size)
        )

        records = await db.fetch_all(query)
        # print("records", records)
        if not records:
            return []
        orders = [OrderOutSchema.model_validate(
            record, from_attributes=True) for record in records]
        # 判断订单是否过期
        for order in orders:
            # print("order", order)
            # print("end_time", order.end_time, type(order.end_time))
            # print("now", datetime.now())
            # 已核销的不改变状态
            if order.status == 2 or order.status == 3:
                continue
            # print("order.end_time", order.end_time, type(order.end_time))
            if order.end_time and order.end_time <= datetime.now():
                order.status = 3
                # 修改状态
                await db.execute(update(OrderModel).where(OrderModel.id == order.id).values({"status": 3}))
        # await db.execute(update(CouponModel).where(CouponModel.id == order.coupon_id).values({"status": 3}))
        # 判断优惠券是否删除 如果删除 提出掉order
        orders = [order for order in orders if order.coupon_id]
        return [order.model_dump(by_alias=True) for order in orders]

    async def detail(self, order_in: OrderDetailInSchema):
        query = (select(
            OrderModel,
            CouponModel.id.label("coupon_id"),
            CouponModel.name.label("coupon_name"),
            CouponModel.min_amount,
            CouponModel.discount_amount,
            CouponModel.min_amount,
            CouponModel.start_time,
            CouponModel.end_time,
            CouponModel.description,
            CouponCateModel.id.label("coupon_cate_id"),
            CouponCateModel.name.label("coupon_cate_name"),

        )
            .select_from(OrderModel)
            .outerjoin(CouponModel, CouponModel.id == OrderModel.coupon_id)
            .outerjoin(CouponCateModel, CouponCateModel.key == CouponModel.cate_id)
            .where(OrderModel.id == order_in.order_id)
        )
        record = await db.fetch_one(query)
        return OrderOutSchema.model_validate(record, from_attributes=True).model_dump(by_alias=True)

    async def app_list(self, app_list_in: OrderAppListInSchema):
        '''
        status：（1待使用2已核销3过期）
        '''
        where = []
        where.append(OrderModel.status == 2)
        offset = (app_list_in.page - 1) * app_list_in.size

        query = (select(
            OrderModel,
            CouponModel.id.label("coupon_id"),
            CouponModel.name.label("coupon_name"),
            CouponModel.min_amount,
            CouponModel.discount_amount,
            CouponModel.min_amount,
            CouponModel.start_time,
            CouponModel.end_time,
            CouponCateModel.id.label("coupon_cate_id"),
            CouponCateModel.name.label("coupon_cate_name"),
        )
            .select_from(OrderModel)
            .outerjoin(CouponModel, CouponModel.id == OrderModel.coupon_id)
            .outerjoin(CouponCateModel, CouponCateModel.key == CouponModel.cate_id)
            .where(*where)
            .offset(offset)
            .limit(app_list_in.size)
        )
        records = await db.fetch_all(query)
        print("records", records)
        if not records:
            return []
        orders = [OrderAppOutSchema.model_validate(
            record, from_attributes=True) for record in records]
        return [order.model_dump(by_alias=True) for order in orders]

    async def app_cancel(self, cancel_in: OrderAppCancleInSchema):
        '''
        核销优惠券
        待使用：1
        已核销：2
        已过期：3
        '''
        order_cancle = await db.fetch_one(
            select(OrderModel)
            .where(
                OrderModel.qr_code == cancel_in.qr_code,
                OrderModel.status == 1)
        )
        if not order_cancle:
            raise AppException(exc=HttpResp.FAILED, message="订单不存在或已核销")
        query = (
            update(OrderModel)
            .where(OrderModel.qr_code == cancel_in.qr_code)
            .values({"status": 2, "cancel_time": datetime.now()})
        )

        return await db.execute(query)

    async def app_statistics(self):
        """
        统计 优惠券使用情况
        待使用：1
        已核销：2
        已过期：3
        """
        now = datetime.now()
        coupon_cate_1_records = await db.fetch_all(
            select(
                OrderModel.id,
            ).select_from(OrderModel)
            .outerjoin(CouponModel, CouponModel.id == OrderModel.coupon_id)
            .outerjoin(CouponCateModel, CouponCateModel.key == CouponModel.cate_id)
            .where(func.date(OrderModel.cancel_time) == now.date(), CouponCateModel.key == 1, OrderModel.status == 2)
        )
        coupon_cate_2_records = await db.fetch_all(
            select(
                OrderModel.id,
            ).select_from(OrderModel)
            .outerjoin(CouponModel, CouponModel.id == OrderModel.coupon_id)
            .outerjoin(CouponCateModel, CouponCateModel.key == CouponModel.cate_id)
            .where(func.date(OrderModel.cancel_time) == now.date(), CouponCateModel.key == 2, OrderModel.status == 2)
        )
        coupon_cate_3_records = await db.fetch_all(
            select(
                OrderModel.id,
            ).select_from(OrderModel)
            .outerjoin(CouponModel, CouponModel.id == OrderModel.coupon_id)
            .outerjoin(CouponCateModel, CouponCateModel.key == CouponModel.cate_id)
            .where(func.date(OrderModel.cancel_time) == now.date(), CouponCateModel.key == 3, OrderModel.status == 2)
        )

        return {
            "coupon_cate_1": len(coupon_cate_1_records),
            "coupon_cate_2": len(coupon_cate_2_records),
            "coupon_cate_3": len(coupon_cate_3_records),
        }
